﻿
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using Components;
using Model;


namespace MsDAL
{
   public partial  class Sto3a00Base
    {		
		/// <summary>
        /// 增加一条数据
        /// </summary>
        public virtual  SqlApp Add(Model.Sto3a00 sto3a00)
        {
			StringBuilder strSql = new StringBuilder();
			strSql.Append("insert into Sto3a00(");
			strSql.Append("stolistid ,docno ,ordate ,prolistid ,providecode ,providename ,tuser ,tel ,fax ,predate ,bsendate ,stocode ,nvarchar(20) ,stuser ,stel ,sfax ,status ,ordamt ,accamt ,gainamt ,gainpct ,dutyamt ,audstatus ,agio1 ,agio2 ,agio3 ,usercode ,listid )");
			strSql.Append(" values (");
			strSql.Append("@stolistid ,@docno ,@ordate ,@prolistid ,@providecode ,@providename ,@tuser ,@tel ,@fax ,@predate ,@bsendate ,@stocode ,@nvarchar(20) ,@stuser ,@stel ,@sfax ,@status ,@ordamt ,@accamt ,@gainamt ,@gainpct ,@dutyamt ,@audstatus ,@agio1 ,@agio2 ,@agio3 ,@usercode ,@listid )");		

			SqlParameter[] parameters = {
				new SqlParameter("@stolistid",SqlDbType.NVarChar),
				new SqlParameter("@docno",SqlDbType.NVarChar),
				new SqlParameter("@ordate",SqlDbType.DateTime),
				new SqlParameter("@prolistid",SqlDbType.NVarChar),
				new SqlParameter("@providecode",SqlDbType.Int),
				new SqlParameter("@providename",SqlDbType.NVarChar),
				new SqlParameter("@tuser",SqlDbType.NVarChar),
				new SqlParameter("@tel",SqlDbType.NVarChar),
				new SqlParameter("@fax",SqlDbType.NVarChar),
				new SqlParameter("@predate",SqlDbType.Int),
				new SqlParameter("@bsendate",SqlDbType.DateTime),
				new SqlParameter("@stocode",SqlDbType.Int),
				new SqlParameter("@nvarchar(20)",SqlDbType.Char),
				new SqlParameter("@stuser",SqlDbType.NVarChar),
				new SqlParameter("@stel",SqlDbType.NVarChar),
				new SqlParameter("@sfax",SqlDbType.NVarChar),
				new SqlParameter("@status",SqlDbType.Int),
				new SqlParameter("@ordamt",SqlDbType.Decimal),
				new SqlParameter("@accamt",SqlDbType.Decimal),
				new SqlParameter("@gainamt",SqlDbType.Decimal),
				new SqlParameter("@gainpct",SqlDbType.Decimal),
				new SqlParameter("@dutyamt",SqlDbType.Decimal),
				new SqlParameter("@audstatus",SqlDbType.Int),
				new SqlParameter("@agio1",SqlDbType.Decimal),
				new SqlParameter("@agio2",SqlDbType.Decimal),
				new SqlParameter("@agio3",SqlDbType.Decimal),
				new SqlParameter("@usercode",SqlDbType.NVarChar),
				new SqlParameter("@listid",SqlDbType.NVarChar)
				};
			parameters[0].Value = sto3a00.Stolistid;
			parameters[1].Value = sto3a00.Docno;
			parameters[2].Value = sto3a00.Ordate;
			parameters[3].Value = sto3a00.Prolistid;
			parameters[4].Value = sto3a00.Providecode;
			parameters[5].Value = sto3a00.Providename;
			parameters[6].Value = sto3a00.Tuser;
			parameters[7].Value = sto3a00.Tel;
			parameters[8].Value = sto3a00.Fax;
			parameters[9].Value = sto3a00.Predate;
			parameters[10].Value = sto3a00.Bsendate;
			parameters[11].Value = sto3a00.Stocode;
			parameters[12].Value = sto3a00.Nvarchar20;
			parameters[13].Value = sto3a00.Stuser;
			parameters[14].Value = sto3a00.Stel;
			parameters[15].Value = sto3a00.Sfax;
			parameters[16].Value = sto3a00.Status;
			parameters[17].Value = sto3a00.Ordamt;
			parameters[18].Value = sto3a00.Accamt;
			parameters[19].Value = sto3a00.Gainamt;
			parameters[20].Value = sto3a00.Gainpct;
			parameters[21].Value = sto3a00.Dutyamt;
			parameters[22].Value = sto3a00.Audstatus;
			parameters[23].Value = sto3a00.Agio1;
			parameters[24].Value = sto3a00.Agio2;
			parameters[25].Value = sto3a00.Agio3;
			parameters[26].Value = sto3a00.Usercode;
			parameters[27].Value = sto3a00.Listid;
			
			return new SqlApp(strSql.ToString(),new ArrayList(parameters), false, "数据保存失败！");
        }
	
        /// <summary>
        /// 判断某个值是否存在表中的某个字段
        /// </summary>
        /// <param name="Condition">条件字段名 例:A=@A</param>
        /// <param name="hs">参数和值 如hs["@A"]=123</param>
        /// <returns></returns>
        public virtual bool Exists(String condition, Hashtable hs)
        {
			StringBuilder strSql = new StringBuilder();
			strSql.Append("select count(1) from Sto3a00 ");
			if (!string.IsNullOrEmpty(condition))
				strSql.Append(" where " + condition);
			List<SqlParameter> list = DBHelper.GetListByHashtable(hs);
			if (list.Count > 0)
				return Convert.ToInt32(DBHelper.ExecuteScalar(strSql.ToString(), list.ToArray())) > 0;
			else
				return Convert.ToInt32(DBHelper.ExecuteScalar(strSql.ToString())) > 0;
        }

		/// <summary>
        /// 更新一条数据
        /// </summary>
        public virtual SqlApp Update(Model.Sto3a00 sto3a00)
        {
			StringBuilder strSql = new StringBuilder();
			strSql.Append("update Sto3a00 set ");
			strSql.Append("stolistid=@stolistid ,docno=@docno ,ordate=@ordate ,prolistid=@prolistid ,providecode=@providecode ,providename=@providename ,tuser=@tuser ,tel=@tel ,fax=@fax ,predate=@predate ,bsendate=@bsendate ,stocode=@stocode ,nvarchar(20)=@nvarchar(20) ,stuser=@stuser ,stel=@stel ,sfax=@sfax ,status=@status ,ordamt=@ordamt ,accamt=@accamt ,gainamt=@gainamt ,gainpct=@gainpct ,dutyamt=@dutyamt ,audstatus=@audstatus ,agio1=@agio1 ,agio2=@agio2 ,agio3=@agio3 ,usercode=@usercode ");
			strSql.Append("where listid=@listid ");
			SqlParameter[] parameters = {
				new SqlParameter("@stolistid", SqlDbType.NVarChar),
				new SqlParameter("@docno", SqlDbType.NVarChar),
				new SqlParameter("@ordate", SqlDbType.DateTime),
				new SqlParameter("@prolistid", SqlDbType.NVarChar),
				new SqlParameter("@providecode", SqlDbType.Int),
				new SqlParameter("@providename", SqlDbType.NVarChar),
				new SqlParameter("@tuser", SqlDbType.NVarChar),
				new SqlParameter("@tel", SqlDbType.NVarChar),
				new SqlParameter("@fax", SqlDbType.NVarChar),
				new SqlParameter("@predate", SqlDbType.Int),
				new SqlParameter("@bsendate", SqlDbType.DateTime),
				new SqlParameter("@stocode", SqlDbType.Int),
				new SqlParameter("@nvarchar(20)", SqlDbType.Char),
				new SqlParameter("@stuser", SqlDbType.NVarChar),
				new SqlParameter("@stel", SqlDbType.NVarChar),
				new SqlParameter("@sfax", SqlDbType.NVarChar),
				new SqlParameter("@status", SqlDbType.Int),
				new SqlParameter("@ordamt", SqlDbType.Decimal),
				new SqlParameter("@accamt", SqlDbType.Decimal),
				new SqlParameter("@gainamt", SqlDbType.Decimal),
				new SqlParameter("@gainpct", SqlDbType.Decimal),
				new SqlParameter("@dutyamt", SqlDbType.Decimal),
				new SqlParameter("@audstatus", SqlDbType.Int),
				new SqlParameter("@agio1", SqlDbType.Decimal),
				new SqlParameter("@agio2", SqlDbType.Decimal),
				new SqlParameter("@agio3", SqlDbType.Decimal),
				new SqlParameter("@usercode", SqlDbType.NVarChar),
				new SqlParameter("@listid", SqlDbType.NVarChar)
				};
			parameters[0].Value = sto3a00.Stolistid;
			parameters[1].Value = sto3a00.Docno;
			parameters[2].Value = sto3a00.Ordate;
			parameters[3].Value = sto3a00.Prolistid;
			parameters[4].Value = sto3a00.Providecode;
			parameters[5].Value = sto3a00.Providename;
			parameters[6].Value = sto3a00.Tuser;
			parameters[7].Value = sto3a00.Tel;
			parameters[8].Value = sto3a00.Fax;
			parameters[9].Value = sto3a00.Predate;
			parameters[10].Value = sto3a00.Bsendate;
			parameters[11].Value = sto3a00.Stocode;
			parameters[12].Value = sto3a00.Nvarchar20;
			parameters[13].Value = sto3a00.Stuser;
			parameters[14].Value = sto3a00.Stel;
			parameters[15].Value = sto3a00.Sfax;
			parameters[16].Value = sto3a00.Status;
			parameters[17].Value = sto3a00.Ordamt;
			parameters[18].Value = sto3a00.Accamt;
			parameters[19].Value = sto3a00.Gainamt;
			parameters[20].Value = sto3a00.Gainpct;
			parameters[21].Value = sto3a00.Dutyamt;
			parameters[22].Value = sto3a00.Audstatus;
			parameters[23].Value = sto3a00.Agio1;
			parameters[24].Value = sto3a00.Agio2;
			parameters[25].Value = sto3a00.Agio3;
			parameters[26].Value = sto3a00.Usercode;
			parameters[27].Value = sto3a00.Listid;
	
			return new SqlApp(strSql.ToString(),new ArrayList(parameters), false, "数据保存失败！");			
		}
		
		public virtual SqlApp Delete(Model.Sto3a00 sto3a00)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("DELETE FROM  Sto3a00  ");
			strSql.Append("WHERE listid=@listid ");
			SqlParameter[] parameters = {
				new SqlParameter("@listid", SqlDbType.NVarChar)
				};
			parameters[0].Value = sto3a00.Listid;
	
			return new SqlApp(strSql.ToString(),new ArrayList(parameters), false, "数据保存失败！");		
		}
		
		/// <summary>
        /// 根据Listid得到整个 Sto3a00对象,如果没查到，返回null
        /// </summary>
        /// <param name="Listid">该实体主键 Listid</param>
        /// <returns>实体Sto3a00的对象</returns>
		public virtual Model.Sto3a00 GetModelById(string Listid)
        {
			string where = "listid=@listid ";
			
			string sql = GetSql(where,null);
			
			List<SqlParameter> parameters = new List<SqlParameter>();
			
			SqlParameter p0 = new SqlParameter("@Listid",SqlDbType.NVarChar);
			p0.Value = Listid;
			parameters.Add(p0);
			
			DataTable dt = DBHelper.GetData(sql, parameters.ToArray());
			
			//调用实体Sto3a00的GetSto3a00ListByDataTable方法得到实体对象
			List<Model.Sto3a00> list =Model.Sto3a00.GetSto3a00ListByDataTable(dt);
			
			return  (list.Count>0) ? list[0] : null;
        }
		
		
		 /// <summary>
        /// 得到Sql语句
        /// </summary>
        /// <param name="Condition">Sql语句的条件</param>
		/// <param name="OrderBy">排序条件，默认为String.Empty</param>
        /// <returns></returns>
        protected virtual String GetSql(string condition,string orderBy)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select [stolistid] ,[docno] ,[ordate] ,[prolistid] ,[providecode] ,[providename] ,[tuser] ,[tel] ,[fax] ,[predate] ,[bsendate] ,[stocode] ,[nvarchar(20)] ,[stuser] ,[stel] ,[sfax] ,[status] ,[ordamt] ,[accamt] ,[gainamt] ,[gainpct] ,[dutyamt] ,[audstatus] ,[agio1] ,[agio2] ,[agio3] ,[usercode] ,[listid] ");
            strSql.Append(" FROM Sto3a00 ");
 			
			if (!string.IsNullOrEmpty(condition)) strSql.Append(" where " + condition);				
			if(!string.IsNullOrEmpty(orderBy)) strSql.Append( " order by " + orderBy );
			
            return  strSql.ToString();
        }

        /// <summary>
        /// 根据条件和参数得到Sto3a00对象
        /// </summary>
        /// <param name="Condition">Sql条件</param>
        /// <param name="hs">参数和值</param>
        /// <returns></returns>
        public virtual Model.Sto3a00 GetModel(String Condition, Hashtable hs)
        {
			String sql = GetSql(Condition,String.Empty);
			DataTable dt = DBHelper.GetTableByCondition(sql,  hs);
			return (dt.Rows.Count > 0) ? Model.Sto3a00.GetSto3a00ListByDataTable(dt)[0] : null;
        }

        /// <summary>
        /// 根据主键的值和某字段得到其值,可以用来判断是否存在
        /// </summary>
        /// <param name="TableKeyValue">主键值</param>
        /// <param name="FieldName">要查询的字段名称</param>
        /// <returns></returns>
        public virtual Object GetValueByField(Object TableKeyValue, Object FieldName)
        {
			StringBuilder strSql = new StringBuilder();
			strSql.Append("select " + FieldName + " from Sto3a00 ");
			strSql.Append(" where listid=@TableKeyValue ");
			SqlParameter p = new SqlParameter("@TableKeyValue", TableKeyValue);

			return DBHelper.ExecuteScalar(strSql.ToString(), p);
        }
		
        /// <summary>
        /// 分页SQL
        /// </summary>
        /// <param name="page"></param>
        /// <returns></returns>
        public virtual ReturnPageData GetList(Pager page)
        {
            ReturnPageData data = new ReturnPageData();
            //page.PageIndex++;
            page.PrimaryKey = "listid ";

            page.ShowFields = " [stolistid] ,[docno] ,[ordate] ,[prolistid] ,[providecode] ,[providename] ,[tuser] ,[tel] ,[fax] ,[predate] ,[bsendate] ,[stocode] ,[nvarchar(20)] ,[stuser] ,[stel] ,[sfax] ,[status] ,[ordamt] ,[accamt] ,[gainamt] ,[gainpct] ,[dutyamt] ,[audstatus] ,[agio1] ,[agio2] ,[agio3] ,[usercode] ,[listid] ";

            page.TableName = " Sto3a00 ";

			page.StrWhere = string.IsNullOrEmpty(page.StrWhere) ? " 1=1 " : page.StrWhere;
			
            page.StrWhere = string.IsNullOrEmpty(page.AutoWhere) ? page.StrWhere : page.StrWhere + " And ("+ page.AutoWhere +")";

            page.For = PagerFor.Count;
            DataTable dtRowCount = DBHelper.GetSelectLimit(page);
            if (dtRowCount == null || dtRowCount.Rows.Count == 0)
            {
                data.AllRowCount = 0;
                return data; //NULL DATA
            }
            data.AllRowCount = Convert.ToInt32(dtRowCount.Rows[0]["Total"]);
            page.For = PagerFor.Data;

            DataTable dtPageData = DBHelper.GetSelectLimit(page);
            data.CurrnetPageData = dtPageData;

            //汇总
            page.For = PagerFor.Summary;
            data.SummaryData = DBHelper.GetSelectLimit(page);
            
            return data;
        }		
		
		public virtual DataTable GetList(string c,Hashtable hs)
		{
			string sql = GetSql(c,null); //得到SQL语句
			//返回结果集
			return DBHelper.GetData(sql,DBHelper.GetListByHashtable(hs).ToArray());
		}
    }
}
